<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of wemPhpExcel
 *
 * @author Fulvio
 */
class wemPhpExcel {
    
    private $cacheMethod;
    private static $cacheMethods=array(
        "memory"=>PHPExcel_CachedObjectStorageFactory::cache_in_memory,
        "apc"=>PHPExcel_CachedObjectStorageFactory::cache_to_apc
    );
    
    private static $types= array(
		'Excel2007',
		'Excel5',
		'Excel2003XML',
		'OOCalc',
		'SYLK',
		'Gnumeric',
		'CSV',
	);
    
    public $openWBs=array();
    public $activeWB;
    public $activeSheet;
    
    private $excelWriter;
    private $excelReader;
    private $fileType;
    private $filter;
    
    
    public function __construct($cache='memory',$fileType='Excel2007'){
                
        
          if (key_exists($cache, wemPhpExcel::$cacheMethods))
                  
              $this->cacheMethod   = wemPhpExcel::$cacheMethods[$cache];
          else
              $this->cacheMethod   = wemPhpExcel::$cacheMethods["memory"];
          
          PHPExcel_Settings::setCacheStorageMethod($this->cacheMethod);

          if (array_search($fileType, wemPhpExcel::$types) !== false)
            $this->fileType=$fileType;
          else{
              throw new Exception("File type ".$fileType." does not exist!. Please select one of the following: \n".implode(";",self::$types) );
              return NULL;
          }
    }
    
    
    public function openWB($file="", $options=array()){
        
        if ($file==""){
            //new void file
                    //excel
            $objPHPExcel = new PHPExcel();

            // Set properties
            $objPHPExcel->getProperties()->setCreator("WEM");
            $objPHPExcel->getProperties()->setLastModifiedBy("WEM");
            
        }else{
            //open existing file ONLY IN READING MODE
            if (isset($this->excelReader))
                unset($this->excelReader);
            
            $this->excelReader = PHPExcel_IOFactory::createReader($this->fileType);  
            
            //set filter
            if (isset($options['filter']))
                $this->excelReader->setReadFilter($filter);
            
            //reads only some worksheets
            //$options['worksheets'] is an array with the worksheets names
            if (isset($options['worksheets']))
                $this->excelReader->setLoadSheetsOnly($options['worksheets']);
            
            //does not read format (->NO TIME cell!)
            if (isset($options['readDataOnly']))
                $this->excelReader->setReadDataOnly(true);
            
            // Read the file
            $objPHPExcel = $this->excelReader->load($file);  
            $objPHPExcel->getProperties()->setLastModifiedBy("WEM");
            }
            
        if ($objPHPExcel){
            $this->openWBs[$objPHPExcel->getProperties()->getTitle()]=$objPHPExcel;
            $this->activeWB=$objPHPExcel;
            $this->activeSheet=$this->activeWB->setActiveSheetIndex(0);
                return true;
            }
            else
                return false;
        
    }
    
    public function saveWB($path, $objPHPExcel=NULL){
        
        if (!isset($objPHPExcel))
            $objPHPExcel=$this->activeWB;
        
        // Write the file
        if (isset($this->excelWriter))
            unset($this->excelWriter);
        
        $this->excelWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $this->fileType);
        $this->excelWriter->setPreCalculateFormulas(false);
        $this->excelWriter->save($path);
    }
    
    public function closeWB($objPHPExcel=NULL){
        
        //insert control on unsaved changes
        if (!isset($objPHPExcel))
            $objPHPExcel=$this->activeWB;
        
        if ($this->activeSheet->getParent() == $objPHPExcel)
            unset($this->activeSheet);
        
        $objPHPExcel->disconnectWorksheets();
        $objPHPExcel->garbageCollect();
        unset($this->openWBs[$objPHPExcel->getProperties()->getTitle()]);
        unset($objPHPExcel);
    }
    
    
    public function close(){
        //insert control on unsaved changes
        if (isset($this->openWBs))
            unset($this->openWBs);
        
        if (isset($this->fileType))
            unset($this->fileType);
        
        if (isset($this->filter))
            unset($this->filter);
        
        if (isset($this->excelWriter))
            unset($this->excelWriter);
        
        if (isset($this->excelReader))
            unset($this->excelReader);
        
        if (isset($this->activeWB))
            unset($this->activeWB);
        
        if (isset($this->activeSheet))
            unset($this->activeSheet);
       
    }
    
    public function setActiveWB($title){
        
        if (key_exists($title, $this->openWBs)){
            $this->activeWB=$this->openWBs[$title];
            return true;
        }else{
            throw new Exception("Workbook ".$title."  not found" );
              return NULL;
        }

    }
    
    public function setActiveSheet($index, $objPHPExcel=NULL){
        
         if (!isset($objPHPExcel))
            $objPHPExcel=$this->activeWB;
        
        //in case the index does not exist phpexcel will throw an exception
        $this->activeSheet=$objPHPExcel->setActiveSheetIndex($index);
        return true;
    }
    
    public function setActiveSheetByName($name, $objPHPExcel=NULL){
        
         if (!isset($objPHPExcel))
            $objPHPExcel=$this->activeWB;
         
        //in case the index does not exist phpexcel will throw an exception
        $this->activeSheet=$objPHPExcel->setActiveSheetIndexByName($name);
        return true;
    }
    
    
    public function setFilter($startRow, $endRow, $startCol, $endCol, $sheetname, $headerRow=NULL){
        if (!isset($this->filter))
            $this->filter=new wemReadFilter();
        $this->filter->AddIOCSheetFilter($startRow, $endRow, $startCol, $endCol, $sheetname, $headerRow);   
    }
    
    public function setChoiceList(PHPExcel_Cell $cell, array $choices){
        
        if (count($choices)<2){
             throw new Exception("Choices must be an array with almost 2 values" );
             return false;
        }
        
        $strChoices='"';
        foreach ($choices as $item){
            $strChoices=$strChoices.$item.",";
        }
        $strChoices = substr_replace($strChoices ,'"',-1);
        
        $objValidation = $cell->getDataValidation();
        $objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
        $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
        $objValidation->setAllowBlank(false);
        $objValidation->setShowInputMessage(true);
        $objValidation->setShowErrorMessage(true);
        $objValidation->setShowDropDown(true);
        $objValidation->setErrorTitle('Input error');
        $objValidation->setError('Value is not in list.');
        $objValidation->setPromptTitle('Pick from list');
        $objValidation->setPrompt('Please pick a value from the drop-down list.');
        
        $objValidation->setFormula1($strChoices);
        
        return $objValidation;
    }

    public function toYml($fileName, $objPHPExcel=NULL)
    {
        if (!isset($objPHPExcel))
            $objPHPExcel=$this->activeWB;

        //CREATE YML destination file
        $fd=fopen($fileName,"w");
        if ($fd){
        
            foreach ($objPHPExcel->getWorksheetIterator() as $sheet){
                //NOME TABELLA
                fwrite($fd,$sheet->getTitle().":\n");

                foreach ($sheet->getRowIterator() as $rowInd => $row){	
                    if ($row->getRowIndex()>1){

                        //YML Label -> 1 SPAZIO
                        $label=$rowInd;
                        fwrite($fd," ".$label.":\n");

                        //INSERIMENTO COLONNE -> 2 SPAZI
                        $cellIterator = $row->getCellIterator();
                        foreach ($cellIterator as $cell) {
                            if (($cell->columnIndexFromString($cell->getColumn())>1) and ($cell->getCalculatedValue()<>"")){
                                //Decide il nome della colonna, controllando se esiste una tabella referenziata da una foreign key (primary key table)
                                $ColumnName=$sheet->getCellByColumnAndRow($cell->columnIndexFromString($cell->getColumn())-1,1)->getCalculatedValue();
                                switch ($ColumnName) 
                                {
                                    case "ip1":
                                    case "ip2":
                                    case "ip3":
                                    case "subnet":
                                    case "gateway":
                                        //CONVERT THE STRING CONTAINING THE IP IN A SIGNED INT (PHP USE ONLY SIGNED INT) AND THEN TO AN UNSIGNED INT FOR MYSQL
                                        $value=sprintf("%u",ip2long($cell->getCalculatedValue()));
                                        break;

                                    case "pac":
                                    case "fac":
                                    case "start":
                                    case "end":
                                        //if (PHPExcel_Shared_Date::isDateTime($cell))
                                            $value=PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), "YYYY/MM/DD");
                                        break;
                                    default:
                                        $value=str_replace(array("\r\n", "\n", "\r","\t",":")," ",$cell->getCalculatedValue()); 
                                }
                                fwrite($fd,"  ".$ColumnName.": ".$value."\n");
                        } 
                }
                    fwrite($fd,"\n");
                }
            }
            fwrite($fd,"\n");
            }

            //CLOSE yml file
            if(fclose($fd)){
                return true;
            }else{
                return false;
            }
            
        }else{
             throw new Exception("Internal Server Error creating ".$fileName );
             return false;
        }
  }
}

/**  Define a Read Filter class implementing PHPExcel_Reader_IReadFilter  */ 
class wemReadFilter implements PHPExcel_Reader_IReadFilter
{ 
        private $_sheetFilter = array();

        /**  Get the list of rows and columns to read  */ 
        public function AddIOCSheetFilter($startRow, $endRow, $startCol, $endCol, $sheetname, $headerRow=NULL) { 

            $this->_sheetFilter[$sheetname]=array(
                "startRow"=>$startRow,
                "endRow"=>$endRow,
                "startCol"=>$startCol,
                "endCol"=>$endCol
                );

            if (isset($headerRow))
                $this->_sheetFilter[$sheetname]["headerRow"]=$headerRow;
            else
                $this->_sheetFilter[$sheetname]["headerRow"]=-1;
        } 

        //is this Cell To be Read?
        //only the cell in the worksheet specified in the filter will be read
        public function readCell($column, $row, $worksheetName = '') {
            if (key_exists($worksheetName, $this->_sheetFilter))
                if (($row >= $this->_sheetFilter[$worksheetName]["startRow"] && $row <= $this->_sheetFilter[$worksheetName]["endRow"]) or ($row==$this->_sheetFilter[$worksheetName]["headerRow"]) ) { 
                    if (in_array($column,range($this->_sheetFilter[$worksheetName]["startCol"],$this->_sheetFilter[$worksheetName]["endCol"])))  { 
                        return true; 
                    } 
                } 
            return false; 
        } 

        public function __toString(){

            return print_r($this->_sheetFilter, true);
        }
} 

?>
